
import sqlite3
from settings import *
import datetime
from command_parse import CommandParse
import logging

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)

'''
CREATE TABLE IF NOT EXISTS sensor_info(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_address TEXT,
    sensor_type TEXT,
    row_command TEXT,
    upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data TEXT
)'''
class SensorInfoTableMethods:

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.conn.rollback()
        else:
            self.conn.commit()
        self.conn.close()
    def __init__(self, db_name='sensor_data.db'):
        self.conn = sqlite3.connect(db_name)
        self.cursor = self.conn.cursor()

    def select_all_sensor_info(self, sensor_address=None, sensor_type=None, start_time=None, end_time=None, limit=30, page=1):

        params = []

        sql = "SELECT * FROM sensor_info"
        if sensor_address:
            sql += " WHERE sensor_address =?"
            
            params.append(sensor_address)

        if sensor_type:
            if params:
                sql += " AND sensor_type =?"
            else:
                sql += " WHERE sensor_type =?"
            
            params.append(sensor_type)

        if start_time:
            if params:
                sql += " AND upload_time >=?"
            else:
                sql += " WHERE upload_time >=?"
            
            params.append(start_time)

        if end_time:
            if params:
                sql += " AND upload_time <=?"
            else:
                sql += " WHERE upload_time <=?"
            
            params.append(end_time)

        if limit:
            sql += " LIMIT ?"
            params.append(limit)

        if page:
            sql += " OFFSET ?"
            params.append((page-1)*limit)

        self.cursor.execute(sql, tuple(params))
        rows = self.cursor.fetchall()

        return rows
    
    def select_all_sensor_info_count(self, sensor_address=None, sensor_type=None, start_time=None, end_time=None):

        if sensor_address:
            sensor_address = sensor_address.lower()
        params = []

        sql = "SELECT count(*) FROM sensor_info"
        if sensor_address:
            sql += " WHERE sensor_address =?"
            
            params.append(sensor_address)

        if sensor_type:
            if params:
                sql += " AND sensor_type =?"
            else:
                sql += " WHERE sensor_type =?"
            
            params.append(sensor_type)

        if start_time:
            if params:
                sql += " AND upload_time >=?"
            else:
                sql += " WHERE upload_time >=?"
            
            params.append(start_time)

        if end_time:
            if params:
                sql += " AND upload_time <=?"
            else:
                sql += " WHERE upload_time <=?"
            
            params.append(end_time)


        self.cursor.execute(sql, tuple(params))
        rows = self.cursor.fetchone()

        return rows['count']
    
    def parse_time(self, time_str): # utc时间转本地时间
        '''sqlite3 数据库 upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP 获取时间是UTC时间，需要转换为本地时间'''

        time_obj = datetime.datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S')

        # 转换为本地时间
        local_time = time_obj + datetime.timedelta(hours=8)

        return local_time
    
    def parse_time_to_utc(self, time_str):
        '''sqlite3 数据库 upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP 获取时间是UTC时间，需要转换为本地时间'''

        time_obj = datetime.datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S')

        # 转换为本地时间
        local_time = time_obj - datetime.timedelta(hours=8)

        return local_time
    
    def get_sensor_info(self, sensor_address):

        sensor_address = sensor_address.lower()

        self.cursor.execute("SELECT * FROM sensor_info WHERE sensor_address = ? order by upload_time desc limit 1", (sensor_address,))

        rows = self.cursor.fetchone()
        return rows
    
    def now_online_status(self, sensor_address): # 当前在线状态

        sensor_address = sensor_address.lower()

        self.cursor.execute("SELECT upload_time FROM sensor_info WHERE sensor_address = ? order by upload_time desc limit 1", (sensor_address,))

        rows = self.cursor.fetchone()

        print(f"rows:{rows}")

        if rows:
            last_upload_time = self.parse_time(rows[0]) # 最后一次上传时间

            time_differences =  (datetime.datetime.now() - last_upload_time).total_seconds()

            # print(time_differences)

            if time_differences < 0:
                return "error" # 时间错误

            # elif time_differences < SEND_INTERVAL*1.5: # 最后一次上传时间和当前时间相差小于SEND_INTERVAL*1.5秒
            elif time_differences < SEND_INTERVAL*2.5: # 最后一次上传时间和当前时间相差小于SEND_INTERVAL*1.5秒
                return "online" # 在线

            else:
                return "offline" # 离线

        else:
            return "offline" # 离线
        

    def interrupted_times_(self, sensor_address, start_time, end_time): # 中断次数

        self.cursor.execute("SELECT upload_time FROM sensor_info WHERE sensor_address = ? AND upload_time >= ? AND upload_time <= ? ", (sensor_address, start_time, end_time))

        rows = self.cursor.fetchall()

        if not rows:
            return {
                "total_upload_times": 0,
                "interrupted_times": 0,
            }
        
        first_upload_time = self.parse_time(rows[1][0]) # 第一次上传时间

        # 计算应该上传的次数
        total_time = (self.parse_time(start_time) - first_upload_time).seconds
        total_upload_times = total_time // SEND_INTERVAL
        total_upload_times += 1 if total_time % SEND_INTERVAL > 0 else 0

        return {
            "total_upload_times": len(rows),
            "interrupted_times": total_upload_times - len(rows),
        }
    
    def interrupted_times(self, sensor_address, start_time, end_time ,is_local_time=True): # 中断次数

        '''如是本地时间，需要转换为utc时间字符串'''

        sensor_address = sensor_address.lower()

        if is_local_time:
            start_time = self.parse_time_to_utc(start_time).strftime('%Y-%m-%d %H:%M:%S')
            end_time = self.parse_time_to_utc(end_time).strftime('%Y-%m-%d %H:%M:%S')
        
        print(f"start_time:{start_time}, end_time:{end_time}")

        self.cursor.execute("SELECT count(*) FROM sensor_info WHERE sensor_address = ? AND upload_time >= ? AND upload_time <= ? ", (sensor_address, start_time, end_time))

        rows = self.cursor.fetchone()

        # print(f"rows:{rows}")

        # 计算应该上传的次数

        # 定义起始时间和结束时间
        start_time = datetime.datetime.strptime(start_time, '%Y-%m-%d %H:%M:%S')
        end_time = datetime.datetime.strptime(end_time, '%Y-%m-%d %H:%M:%S')
        # 计算时间差
        total_time = (end_time - start_time).total_seconds()

        logger.info(f"start_time:{start_time}, end_time:{end_time}")

        logger.info(f"\(end_time - start_time\)\.total_seconds\(\): total_time: {total_time}")

        should_upload_times = total_time // SEND_INTERVAL

        logger.info(f"should_upload_times: {should_upload_times}")
        should_upload_times += 1 if total_time % SEND_INTERVAL > 0 else 0

        return {
            "total_upload_times": rows[0],
            "interrupted_times": should_upload_times - rows[0],
            "should_upload_times": should_upload_times
        }
    
    def insert_sensor_data(self, data):

        '''
        插入传感器交来的数据
        data: E5 1D 0000 12 0001 0021010012180908 00 00000000 64 00 00 00 02 0107 21EE
        '''

        cp = CommandParse(data)

        sensor_info = cp.parse_result

        devaddr = sensor_info["devaddr"]
        dev_type = sensor_info["dev_type"]


        if dev_type in ["压力", "倾角"]:

            sql = "insert into sensor_info(sensor_address, sensor_type, row_command) VALUES (?,?,?)"

            self.cursor.execute(sql, (devaddr, dev_type, data))

        return sensor_info
    
    def select_sensor_data(self, sensor_address, start_time=None, end_time=None):



        sensor_address = sensor_address.lower()

        if start_time:
            start_time = self.parse_time_to_utc(start_time).strftime('%Y-%m-%d %H:%M:%S')
        
        else:
            start_time = (datetime.datetime.now() - datetime.timedelta(hours=4)).strftime('%Y-%m-%d %H:%M:%S')
            
            start_time = self.parse_time_to_utc(start_time).strftime('%Y-%m-%d %H:%M:%S')

        if end_time:
            end_time = self.parse_time_to_utc(end_time).strftime('%Y-%m-%d %H:%M:%S')

        else:
            end_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            
            end_time = self.parse_time_to_utc(end_time).strftime('%Y-%m-%d %H:%M:%S')

        self.cursor.execute("SELECT * FROM sensor_info WHERE sensor_address = ? AND upload_time >= ? AND upload_time <= ? ", (sensor_address, start_time, end_time))

        rows = self.cursor.fetchall()

        print(f"sensor_address:{sensor_address}")

        print(f"start_time:{start_time}, end_time:{end_time}")

        print(f"select_sensor_data rows:{rows}")

        return rows
    
if __name__ == '__main__':
    db_name = 'sensor_data.db'

    sensor_info_table_methods = SensorInfoTableMethods(db_name)

    with SensorInfoTableMethods(db_name) as sensor_info_table_methods:

        print(sensor_info_table_methods.select_all_sensor_info())
        # print(sensor_info_table_methods.select_all_sensor_info_count())
